// readers/sqlserver_reader.go
package readers

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"mig2kwdb/common"
	"reflect"
	"strings"

	_ "github.com/zhanghaiyang9999/go-mssqldb"
)

type SQLServerReader struct {
	db *sql.DB
}

// NewSQLServerReader 创建新的 SQLServerReader 实例并初始化数据库连接
func NewSQLServerReader(connInfo common.DBConnectionInfo) (*SQLServerReader, error) {
	// 构建 SQL Server 连接字符串
	// 示例: server=localhost;user id=sa;password=yourpassword;port=1433;database=yourdatabase
	connStr := "server=" + connInfo.Ip

	if len(connInfo.InstanceName) > 0 {
		connStr += "\\" + connInfo.InstanceName
	}
	if connInfo.Port != 0 {
		connStr += fmt.Sprintf(";port=%d;", connInfo.Port)
	}
	connStr += ";user id=" + connInfo.User + ";password=" + connInfo.Password + ";"
	if connInfo.SSL {
		connStr += ";encrypt=true;"
	} else {
		connStr += ";encrypt=disable;"
	}
	db, err := sql.Open("sqlserver", connStr)
	if err != nil {
		return nil, err
	}

	return &SQLServerReader{
		db: db,
	}, nil
}
func (r *SQLServerReader) GetObjectName(name string) string {

	return `[` + name + `]`

}
func (r *SQLServerReader) IsNetworkError(err error) bool {
	etype := reflect.TypeOf(err)
	name := etype.String()
	return !strings.EqualFold(name, "mssql.Error")
}
func (r *SQLServerReader) GetDB() *sql.DB {
	return r.db
}
func (r *SQLServerReader) GetStartPos() (common.ReplicateStartPos, error) {
	return common.ReplicateStartPos{}, nil
}
func (r *SQLServerReader) SetDataBases(dataBases []common.DatabaseConfig) {

}
func (r *SQLServerReader) SetReplicateHandler(handler common.ReplicateHandlerInterface) {
}
func (r *SQLServerReader) StartReplicate(ctx context.Context, startPos common.ReplicateStartPos) error {
	return nil
}
func (r *SQLServerReader) Close() {
	if r.db != nil {
		r.db.Close()
	}
}
func (r *SQLServerReader) GetDataBaseType() string {
	return "sqlserver"
}

// GetDataBases retrieves a list of all databases in the SQL Server instance
func (r *SQLServerReader) GetDataBases() ([]string, error) {
	query := `
		SELECT name 
		FROM sys.databases 
		WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') -- 排除系统数据库
		ORDER BY name
	`

	rows, err := r.db.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var databases []string
	for rows.Next() {
		var dbName string
		if err := rows.Scan(&dbName); err != nil {
			return nil, err
		}
		databases = append(databases, dbName)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return databases, nil
}

// GetDatabaseTables retrieves a list of all tables in the specified database
func (r *SQLServerReader) GetDatabaseTables(databaseName, schemaName string) ([]string, error) {
	// 如果没有提供 schemaName，则默认使用 'dbo'
	if schemaName == "" {
		schemaName = "dbo"
	}

	// 注意：这里需要切换到指定的数据库上下文或者在查询中指定数据库名
	// 一种方式是使用 USE database 语句，另一种是在查询中指定完整名称
	// 我们选择在查询中指定完整名称
	query := `USE  ` + databaseName + `;` + `SELECT t.name AS table_name
		FROM sys.tables t
		INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
		WHERE s.name = '` + schemaName + `' ORDER BY t.name`

	rows, err := r.db.Query(query)
	if err != nil {
		log.Println("Error querying tables:", err)
		return nil, err
	}
	defer rows.Close()

	var tables []string
	for rows.Next() {
		var tableName string
		if err := rows.Scan(&tableName); err != nil {
			return nil, err
		}
		tables = append(tables, tableName)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return tables, nil
}
func (r *SQLServerReader) MapDataTypes(column []common.KwdbColumn) []common.KwdbColumn {
	for i := range column {
		switch strings.ToUpper(column[i].Type) {
		case "BIT":
			column[i].Type = common.DataTypes.BOOL.Name
		case "YEAR", "TINYINT", "SMALLINT":
			column[i].Type = common.DataTypes.INT2.Name
		case "MEDIUMINT", "INT", "INTEGER":
			column[i].Type = common.DataTypes.INT4.Name
		case "IMAGE", "TINYBLOB", "MEDIUMBLOB", "BLOB", "LONGBLOB", "BINARY", "VARBINARY":
			column[i].Type = common.DataTypes.BLOB.Name

		case "FLOAT", "REAL", "DOUBLE":
			column[i].Type = common.DataTypes.FLOAT8.Name
		case "SMALLMONEY", "MONEY", "DECIMAL", "NUMERIC":
			column[i].Type = common.DataTypes.DECIMAL.Name

		case "TIMESTAMP WITH TIME ZONE", "DATETIMEOFFSET":
			column[i].Type = common.DataTypes.TIMESTAMPTZ.Name

		case "TIME", "TIME WITHOUT TIME ZONE":
			column[i].Type = common.DataTypes.TIME.Name
		case "SMALLDATETIME", "DATETIME", "TIMESTAMP", "DATETIME2":
			column[i].Type = common.DataTypes.TIMESTAMP.Name
		case "TEXT", "TINYTEXT", "MEDIUMTEXT", "LONGTEXT":
			column[i].Type = common.DataTypes.TEXT.Name
		case "NTEXT":
			column[i].Type = common.DataTypes.NVARCHAR.Name

		default:
		}
	}
	return column

}
func (r *SQLServerReader) GetTableDefinition(databaseName, schemaName, tableName string) (common.TableDefinition, error) {
	var err error
	var def common.TableDefinition
	def.DatabaseName = databaseName
	def.SchemaName = schemaName
	def.TableName = tableName
	def.Columns, err = r.GetTableColumn(databaseName, schemaName, tableName)
	if err != nil {
		log.Println("Error querying table columns:", err)
		return def, err
	}
	

	return def, nil
}

// GetTableIndexes retrieves index information for a specific table in a SQL Server database
func (r *SQLServerReader) GetTableIndexes(databaseName, schemaName, tableName string) ([]common.IndexInfo, error) {
	// 如果没有提供 schemaName，则默认使用 'dbo'
	if schemaName == "" {
		schemaName = "dbo"
	}

	query := `
		USE ` + databaseName + `;
		SELECT 
			i.name AS index_name,
			i.is_unique,
			i.is_primary_key,
			ic.index_column_id,
			c.name AS column_name,
			ic.is_descending_key
		FROM sys.indexes i
		INNER JOIN sys.tables t ON i.object_id = t.object_id
		INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
		INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
		INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
		WHERE s.name = @p1 
			AND t.name = @p2 
			AND i.name IS NOT NULL
		ORDER BY i.name, ic.index_column_id
	`

	rows, err := r.db.Query(query, schemaName, tableName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// 使用 map 来组织索引信息
	indexMap := make(map[string]*common.IndexInfo)

	for rows.Next() {
		var indexName string
		var isUnique bool
		var isPrimary bool
		var columnSeq int
		var columnName string
		var isDescending bool

		err := rows.Scan(&indexName, &isUnique, &isPrimary, &columnSeq, &columnName, &isDescending)
		if err != nil {
			return nil, err
		}

		// 如果索引不存在，创建新的索引信息
		if _, exists := indexMap[indexName]; !exists {
			indexInfo := &common.IndexInfo{
				Name:      indexName,
				IsUnique:  isUnique,
				IsPrimary: isPrimary,
				Type:      "INDEX",
			}

			// 确定索引类型
			if isPrimary {
				indexInfo.Type = "PRIMARY"
			} else if isUnique {
				indexInfo.Type = "UNIQUE"
			}

			indexMap[indexName] = indexInfo
		}

		// 添加列信息到索引中
		indexColumn := common.IndexColumn{
			Name:      columnName,
			Position:  columnSeq,
			SortOrder: "ASC",
		}

		// 根据排序规则确定排序顺序
		if isDescending {
			indexColumn.SortOrder = "DESC"
		}

		indexMap[indexName].Columns = append(indexMap[indexName].Columns, indexColumn)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	// 将 map 转换为 slice
	var indexes []common.IndexInfo
	for _, indexInfo := range indexMap {
		indexes = append(indexes, *indexInfo)
	}

	return indexes, nil
}

// GetTableColumn retrieves column information for a specific table in a SQL Server database
func (r *SQLServerReader) GetTableColumn(databaseName, schemaName, tableName string) ([]common.KwdbColumn, error) {
	// 如果没有提供 schemaName，则默认使用 'dbo'
	if schemaName == "" {
		schemaName = "dbo"
	}

	// 查询列信息
	query := `USE ` + databaseName + `;
		SELECT 
			c.name AS column_name,
			c.column_id AS ordinal_position,
			UPPER(ty.name) AS data_type,
			COALESCE(c.max_length, 0) AS character_maximum_length,
			COALESCE(c.precision, 0) AS numeric_precision,
			COALESCE(c.scale, 0) AS numeric_scale,
			CASE WHEN c.is_nullable = 1 THEN 'YES' ELSE 'NO' END AS is_nullable,
			ISNULL(CONVERT(nvarchar(max), dc.definition), '') AS column_default,
			CASE WHEN ic.object_id IS NOT NULL THEN 'YES' ELSE 'NO' END AS is_primary_key,
			CASE WHEN uc.object_id IS NOT NULL THEN 'YES' ELSE 'NO' END AS is_unique,
			ISNULL(ep.value, '') AS column_comment,
			CASE WHEN c.is_identity = 1 THEN 'YES' ELSE 'NO' END AS auto_increment,
			'' AS character_set_name, -- SQL Server 不使用字符集概念
			'' AS collation_name -- 简化处理，实际可从 c.collation_name 获取
		FROM sys.columns c
		INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id
		LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
		LEFT JOIN (
			SELECT ic.object_id, ic.column_id
			FROM sys.index_columns ic
			INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
			WHERE i.is_primary_key = 1
		) ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
		LEFT JOIN (
			SELECT DISTINCT ic.object_id, ic.column_id
			FROM sys.index_columns ic
			INNER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
			WHERE i.is_unique = 1
		) uc ON c.object_id = uc.object_id AND c.column_id = uc.column_id
		LEFT JOIN sys.extended_properties ep ON c.object_id = ep.major_id AND c.column_id = ep.minor_id AND ep.name = 'MS_Description'
		INNER JOIN sys.tables t ON c.object_id = t.object_id
		INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
		WHERE s.name = @p1 AND t.name = @p2 AND DB_NAME() = @p3
		ORDER BY c.column_id
	`

	rows, err := r.db.Query(query, schemaName, tableName, databaseName)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var columns []common.KwdbColumn
	for rows.Next() {
		var col common.KwdbColumn

		// 扫描查询结果
		err := rows.Scan(
			&col.Name,
			&col.Position,
			&col.Type,
			&col.Length,
			&col.Precision,
			&col.Scale,
			&col.Nullable,
			&col.DefaultValue,
			&col.PrimaryKey,
			&col.Unique,
			&col.Comment,
			&col.AutoIncrement,
			&col.Charset,
			&col.Collation,
		)
		if err != nil {
			return nil, err
		}

		columns = append(columns, col)
	}

	if err = rows.Err(); err != nil {
		return nil, err
	}

	return r.MapDataTypes(columns), nil
}
func (r *SQLServerReader) GetConditionRows(databaseName, schemaName, tableName string, columns []common.KwdbColumn, condition string) (common.RowsInterface, error) {
	orderby := ""
	keyNum := 0
	sql := "select "
	for idx, col := range columns {
		if idx > 0 {
			sql += ","
		}
		sql += " " + r.GetObjectName(col.Name)
		if col.PrimaryKey.String == "YES" {
			if keyNum > 0 {
				orderby += ","
			}
			orderby += " " + r.GetObjectName(col.Name)
			keyNum++
		}
	}
	//如果没有主键，则使用唯一索引
	if orderby == "" {
		keyNum = 0
		for _, col := range columns {
			if col.Unique.String == "YES" {
				if keyNum > 0 {
					orderby += ","
				}
				orderby += " " + r.GetObjectName(col.Name)
				keyNum++
			}
		}
	}

	sql += " from " + r.GetObjectName(databaseName) + "." + r.GetObjectName(schemaName) + "." + r.GetObjectName(tableName)
	if condition != "" {
		sql += " where " + condition
	}
	if orderby != "" {
		sql += " order by " + orderby
	}

	rows, err := r.db.Query(sql)
	return common.WrapSQLRows(rows), err
}
